package com.example.excel;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import java.io.FileOutputStream;
import java.util.*;

public class DuplicateChecker {

    private static List<List<String>> transpose(List<List<String>> data) {
        int rows = data.size();
        int cols = data.get(0).size();

        List<List<String>> transposed = new ArrayList<>();
        for (int i = 0; i < cols; i++) {
            List<String> colList = new ArrayList<>();
            for (int j = 0; j < rows; j++) {
                colList.add(data.get(j).get(i));
            }
            transposed.add(colList);
        }
        return transposed;
    }

    public static void main(String[] args) throws Exception {
        // 创建工作簿
        SXSSFWorkbook workbook = new SXSSFWorkbook();





        // 创建一些数据
        List<List<String>> data = Arrays.asList(
            Arrays.asList("测试", "授权到人：M57025", "折扣计划管理", "电商折扣计划管理", "导出源文件"),
            Arrays.asList("测试", "授权到人：M57025", "折扣计划管理", "季度电商折扣计划", "导出折扣数据"),
            Arrays.asList("测试", "授权到人：M57025", "折扣计划管理", "季度电商折扣计划", "查看支持文件"),
            Arrays.asList("电商-SP&O-商务", "授权到人：M570255", "折扣兑付管理", "电商折扣兑付管理", "导出版本数据")
        );

        // 转置矩阵，以便我们可以按列检查
        List<List<String>> transposedData = transpose(data);

        List<MergeRegion> mergeRegions = new ArrayList<>();
        for (int colIndex = 0; colIndex < transposedData.size(); colIndex++) {
            if (colIndex == 1) continue;
            System.out.println("Checking column: " + colIndex);
            Map<String, Integer> elementIndexes = new HashMap<>();
            for (int rowIndex = 0; rowIndex < transposedData.get(colIndex).size()-1; rowIndex++) {
                String element = transposedData.get(colIndex).get(rowIndex);
                String nextElement = transposedData.get(colIndex).get(rowIndex+1);

                if (elementIndexes.containsKey(element)) {
                    if (!element.equals(nextElement)){
                        System.out.println("Element '" + element + "' is duplicated in column " + colIndex
                            + ". First occurrence at row index " + elementIndexes.get(element)
                            + ", and duplicate found at row index " + rowIndex);
                        MergeRegion mergeRegion = new MergeRegion(elementIndexes.get(element) + 1, rowIndex + 1, colIndex, colIndex);
                        mergeRegions.add(mergeRegion);
                        if (colIndex==0){
                            MergeRegion mergeRegion1 = new MergeRegion(elementIndexes.get(element) + 1, rowIndex + 1, colIndex+1, colIndex+1);
                            mergeRegions.add(mergeRegion1);
                        }

                    }
                } else {
                    elementIndexes.put(element, rowIndex);
                }
            }
        }



        // 创建一个自定义写入处理器来应用合并区域
        SheetWriteHandler mergeHandler = new SheetWriteHandler() {
            @Override
            public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
                applyMergeRegions(writeWorkbookHolder, mergeRegions);
            }
        };
        CellWriteHandler cellWriteHandler = new CellWriteHandler() {
            @Override
            public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
                Sheet sheet = writeSheetHolder.getSheet();
                Workbook workbook = sheet.getWorkbook();
                if (!isHead) { // 非表头，设置单元格内容自动换行

                    CellStyle cellStyle = workbook.createCellStyle();
                    cellStyle.setWrapText(true); // 设置单元格自动换行

                    // 水平和垂直居中
//            cellStyle.setAlignment(HorizontalAlignment.CENTER);
                    cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

                    cell.setCellStyle(cellStyle);
                }
                sheet.setColumnWidth(0, 30 * 256);
                sheet.setColumnWidth(1, 60 * 256);
                for (int i = 2; i < 5; i++) {
                    sheet.setColumnWidth(i, 40 * 256);
                }

            }
        };

        List<List<String>> header = new ArrayList<>();
        List<String> Header1 = new ArrayList<>();
        Header1.add("Header1");

        List<String> Header2 = new ArrayList<>();
        Header2.add("Header2");
        List<String> Header3 = new ArrayList<>();
        Header3.add("Header3");
        List<String> Header4 = new ArrayList<>();
        Header4.add("Header4");
        List<String> Header5 = new ArrayList<>();
        Header5.add("Header5");
        header.add(Header1);
        header.add(Header2);
        header.add(Header3);
        header.add(Header4);
        header.add(Header5);


        // 写入数据到Excel文件
        EasyExcel.write(new FileOutputStream("workbook.xlsx"), data.getClass())
            .registerWriteHandler(mergeHandler)
            .sheet("Sheet1")
            .head(header)
            .doWrite(data);

        // 关闭工作簿
        workbook.close();
    }

    private static void applyMergeRegions(WriteWorkbookHolder writeWorkbookHolder, List<MergeRegion> mergeRegions) {
        Sheet sheet = writeWorkbookHolder.getWorkbook().getSheetAt(0);
        for (MergeRegion mergeRegion : mergeRegions) {
            sheet.addMergedRegion(mergeRegion.toCellRangeAddress());
        }
    }
}